package mwt.xml.xdbforms.dbms.metadata.impl;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;
import mwt.xml.xdbforms.dbms.metadata.MetadataManager;

public class MySQLMetadataManager implements MetadataManager {
    private Connection connection;

    public MySQLMetadataManager(Connection connection) {
        this.connection = connection;
    }

    @Override
    public List<String> getSchemasList() throws SQLException {
        Statement st = null;
        ResultSet rs = null;
        List<String> schemas = new ArrayList<String>();

        try {
            st = this.connection.createStatement();
            rs = st.executeQuery("SHOW DATABASES");

            while (rs.next()) {
                schemas.add(rs.getString("Database"));
            }
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch(SQLException ex) {}
            }

            if (st != null) {
                try {
                    st.close();
                } catch(SQLException ex) {}
            }
        }

        return schemas;
    }

    @Override
    public List<TableMetadata> getTableList(String schemaName) throws SQLException {
        DatabaseMetaData dbMd = null;
        ResultSet rs = null;
        List<TableMetadata> tables = new ArrayList<TableMetadata>();
        TableMetadata table = null;

        try {
            dbMd = this.connection.getMetaData();
            rs = dbMd.getTables(schemaName, null, "%", null);

            while (rs.next()) {
                table = new TableMetadata(schemaName,//rs.getString("TABLE_SCHEM"),
                                          rs.getString("TABLE_NAME"),
                                          rs.getString("TABLE_TYPE"),
                                          rs.getString("REMARKS"));
                tables.add(table);
            }
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch(SQLException ex) {}
            }
        }

        return tables;
    }

    @Override
    public TableMetadata getTableMetadata(String schemaName, String tableName) throws SQLException {
        DatabaseMetaData dbMd = null;

        ResultSet rsTab = null;
        ResultSet rsCols = null;
        ResultSet rsIfks = null;
        ResultSet rsEfks = null;
        ResultSet rsPks = null;

        TableMetadata table = null; //new TableMetadata();

        Set<ColumnMetadata> columns = new TreeSet<ColumnMetadata>();
        Set<ForeignKeyMetadata> ifks = new HashSet<ForeignKeyMetadata>();
        Set<ForeignKeyMetadata> efks = new HashSet<ForeignKeyMetadata>();
        Set<PrimaryKeyMetadata> pks = new HashSet<PrimaryKeyMetadata>();

        ColumnMetadata column = null;
        ForeignKeyMetadata ifk = null;
        ForeignKeyMetadata efk = null;
        PrimaryKeyMetadata pk = null;

        try {
            dbMd = this.connection.getMetaData();

            rsTab = dbMd.getTables(schemaName, null, tableName, null);
            /* populate basic table info */
            if (rsTab.next()) {
                table = new TableMetadata(schemaName,//rsTab.getString("TABLE_SCHEM"),
                                          rsTab.getString("TABLE_NAME"),
                                          rsTab.getString("TABLE_TYPE"),
                                          rsTab.getString("REMARKS"));
            }

            rsEfks = dbMd.getExportedKeys(schemaName, null, tableName);
            rsIfks = dbMd.getImportedKeys(schemaName, null, tableName);
            rsPks = dbMd.getPrimaryKeys(schemaName, null, tableName);
            rsCols = dbMd.getColumns(schemaName, null, tableName, "%");

            /* Populate table columns information */
            while (rsCols.next()) {
                column = new ColumnMetadata(rsCols.getString("COLUMN_NAME"),
                                            rsCols.getInt("DATA_TYPE"),
                                            rsCols.getString("TYPE_NAME"),
                                            rsCols.getString("COLUMN_SIZE"),
                                            rsCols.getInt("NULLABLE"),
                                            rsCols.getString("REMARKS"),
                                            rsCols.getString("COLUMN_DEF"),
                                            rsCols.getString("IS_NULLABLE"),
                                            rsCols.getString("SCOPE_CATALOG"),
                                            rsCols.getString("SCOPE_SCHEMA"),
                                            rsCols.getString("SCOPE_TABLE"),
                                            rsCols.getShort("SOURCE_DATA_TYPE"),
                                            rsCols.getString("IS_AUTOINCREMENT"),
                                            rsCols.getInt("ORDINAL_POSITION"));
                columns.add(column);
            }

            /* Populate table primary keys */
            while (rsPks.next()) {
                pk = new PrimaryKeyMetadata(rsPks.getString("COLUMN_NAME"),
                                            rsPks.getShort("KEY_SEQ"),
                                            rsPks.getString("PK_NAME"));

                pks.add(pk);
            }

            /* Populate the exported table foreign keys */
            while (rsEfks.next()) {
                efk = new ForeignKeyMetadata(rsEfks.getString("PKTABLE_NAME"),
                                             rsEfks.getString("PKCOLUMN_NAME"),
                                             rsEfks.getString("FKTABLE_NAME"),
                                             rsEfks.getString("FKCOLUMN_NAME"),
                                             rsEfks.getShort("KEY_SEQ"),
                                             rsEfks.getShort("UPDATE_RULE"),
                                             rsEfks.getShort("DELETE_RULE"),
                                             rsEfks.getString("FK_NAME"),
                                             rsEfks.getString("PK_NAME"));
                efks.add(efk);
            }

            /* Populate the imported table foreign keys */
            while (rsIfks.next()) {
                ifk = new ForeignKeyMetadata(rsIfks.getString("PKTABLE_NAME"),
                                             rsIfks.getString("PKCOLUMN_NAME"),
                                             rsIfks.getString("FKTABLE_NAME"),
                                             rsIfks.getString("FKCOLUMN_NAME"),
                                             rsIfks.getShort("KEY_SEQ"),
                                             rsIfks.getShort("UPDATE_RULE"),
                                             rsIfks.getShort("DELETE_RULE"),
                                             rsIfks.getString("FK_NAME"),
                                             rsIfks.getString("PK_NAME"));
                ifks.add(ifk);
            }

            table.setColumns(columns);
            table.setPks(pks);
            table.setExportedKeys(efks);
            table.setImportedKeys(ifks);
        } finally {
            if (rsTab != null) {
                try {
                    rsTab.close();
                } catch(SQLException ex) {}
            }
            if (rsCols != null) {
                try {
                    rsCols.close();
                } catch(SQLException ex) {}
            }
            if (rsIfks != null) {
                try {
                    rsIfks.close();
                } catch(SQLException ex) {}
            }
            if (rsEfks != null) {
                try {
                    rsEfks.close();
                } catch(SQLException ex) {}
            }
            if (rsPks != null) {
                try {
                    rsPks.close();
                } catch(SQLException ex) {}
            }
        }

        return table;
    }
}
